﻿
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Threading.Tasks;
 
namespace CQIE.LabAssist.PublicHelp
{
    public class NpoiExcelUtility
{
    private string _xlsPath = string.Empty;
    private XSSFWorkbook _workBook = null;//.xlsx
                                          //private HSSFWorkbook _workBook = null;//.xls如果需要这种格式可用HSSFWorkbook


    /// <summary>
    /// 构造函数
    /// </summary>
    /// <param name="xlsPath">xls保存路径</param>
    /// <param name="TempletFileName">xls模板路径</param>
    public NpoiExcelUtility(string xlsPath, string TempletFileName)
    {
        _xlsPath = this.CheckFilePath(xlsPath);

        FileStream file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
        _workBook = new XSSFWorkbook(file);
    }

    /// <summary>
    /// 将DataTable保存到sheet里
    /// </summary>
    /// <param name="dt"></param>
    /// <param name="sheet"></param>
    private void DataTableToExcel(DataTable dt, ISheet sheet)
    {
        ICellStyle style = _workBook.CreateCellStyle();
        style.Alignment = HorizontalAlignment.Left;
        style.VerticalAlignment = VerticalAlignment.Center;

        ICellStyle colStyle = _workBook.CreateCellStyle();
        colStyle.Alignment = HorizontalAlignment.Left;
        colStyle.VerticalAlignment = VerticalAlignment.Center;
        IFont font = _workBook.CreateFont();
        font.Color = NPOI.HSSF.Util.HSSFColor.LightBlue.Index;
        colStyle.SetFont(font);


        //列名
        //IRow row = sheet.CreateRow(0);
        //for (int i = 0; i < dt.Columns.Count; i++)
        //{
        //    sheet.SetDefaultColumnStyle(i, style);

        //    ICell cell = row.CreateCell(i);
        //    cell.SetCellValue(dt.Columns[i].ToString());

        //    cell.CellStyle = colStyle;
        //}
        //内容
        //var headerRow = (HSSFRow)sheet.GetRow(0);
        var headerRow = (XSSFRow)sheet.GetRow(0);
        for (int i = 1; i <= dt.Rows.Count; i++)
        {
            IRow row = sheet.CreateRow(i + 6);//这里是从第几行开始写数据，我的模板前几行都是样式  所以从第六行开始写格式
            row.Height = 50 * 20;
            //ICell numcell = row.CreateCell(0);
            //numcell.SetCellValue(i);
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                object obj = dt.Rows[i - 1][j];
                if (obj != null)
                {
                    //string ColumnName = dt.Columns[j].ToString();
                    //var _Column = headerRow.Cells.Find(t => !string.IsNullOrEmpty(t.StringCellValue) && t.ToString().ToLower() == ColumnName.ToLower());
                    //ICell cell = row.CreateCell(j + 1);             
                    //if (_Column != null)
                    //{
                    ICell cell = row.CreateCell(j/*_Column.ColumnIndex*/);
                    if (obj is double || obj is float || obj is int || obj is long || obj is decimal)
                    {
                        cell.SetCellValue(Convert.ToDouble(obj));
                    }
                    else if (obj is bool)
                    {
                        cell.SetCellValue((bool)obj);
                    }
                    else
                    {
                        cell.SetCellValue(obj.ToString());
                    }
                    // }
                }
            }
        }
    }

    /// <summary>
    /// 保存Excel
    /// </summary>
    public void SaveExcel()
    {
        FileStream file = new FileStream(_xlsPath, FileMode.Create);
        _workBook.Write(file);
        file.Close();
    }

    /// <summary>
    /// 创建Sheet
    /// </summary>
    /// <param name="sheetName">sheet名称</param>
    /// <param name="tbl">DataTable数据表，当行数大于65536时，自动分割成几个sheet，sheet名称为sheetName_i</param>
    public void CreatExcelSheet(string sheetName, DataTable tbl)
    {
        string sName = this.CheckSheetName(sheetName);

        int rowMax = 65535;
        int intNum = tbl.Rows.Count / rowMax;
        int remainder = tbl.Rows.Count % rowMax;

        for (int i = 0; i < intNum; i++)
        {
            DataTable subTbl = tbl.Clone();
            for (int j = 0; j < 65535; j++)
            {
                int rowIndex = i * rowMax + j;
                subTbl.Rows.Add(tbl.Rows[rowIndex].ItemArray);
            }
            string subSheet = sName + "_" + (i + 1);
            //ISheet sheet = _workBook.CreateSheet(subSheet);
            ISheet sheet = _workBook.GetSheetAt(0);
            this.DataTableToExcel(subTbl, sheet);
        }
        if (remainder > 0)
        {
            DataTable subTbl = tbl.Clone();
            for (int j = 0; j < remainder; j++)
            {
                int rowIndex = intNum * rowMax + j;
                subTbl.Rows.Add(tbl.Rows[rowIndex].ItemArray);
            }
            string subSheet = sName + "_" + (intNum + 1);
            if (intNum < 1)
            {
                subSheet = sName;
            }
            //ISheet sheet = _workBook.CreateSheet(subSheet);
            ISheet sheet = _workBook.GetSheetAt(0);
            this.DataTableToExcel(subTbl, sheet);
        }
    }

    /// <summary>
    /// 检查sheet名称是否合法，并去掉不合法字符
    /// </summary>
    /// <param name="sheetName"></param>
    private string CheckSheetName(string sheetName)
    {
        string rlt = sheetName;
        string[] illegalChars = { "*", "?", "\"", @"\", "/" };
        for (int i = 0; i < illegalChars.Length; i++)
        {
            rlt = rlt.Replace(illegalChars[i], "");
        }
        return rlt;
    }

    /// <summary>
    ///  检查xls路径是否合法，并去掉不合法字符
    /// </summary>
    /// <param name="filePath"></param>
    private string CheckFilePath(string filePath)
    {
        string dir = Path.GetDirectoryName(filePath);
        string fileName = Path.GetFileNameWithoutExtension(filePath);
        string ext = Path.GetExtension(filePath);

        string[] illegalChars = { ":", "*", "?", "\"", "<", ">", "|", @"\", "/" };
        for (int i = 0; i < illegalChars.Length; i++)
        {
            fileName = fileName.Replace(illegalChars[i], "");
        }
        string rlt = Path.Combine(dir, fileName + ext);
        return rlt;
    }
}
}